﻿using System.Collections.Generic;
using System.Linq;
using WMSSyncData.Model;
using WMSSyncData.Utils;

namespace WMSSyncData.DAL.SqlDB
{
    public class Tddev102DAL : BaseSqlDBDAL<tddev102>
    {
        public List<tddev102> GetSyncData()
        {
            var sql = @"SELECT BillNo as WMSPO, NEWID() AS guid,RelatedBill as orno,RowNo as pono,OrderNo as sqnb,case BillType when '采购入库' then '收货' else BillType end  as ittp  ,
PartNumber as item,temp.Unit cuqp, temp.Qty reci,'2'as fire,ISNULL(Ext3,Storage_SN) as cwar, ' ' as rclo ,getdate() as edte1,' ' AS mess,0 AS refcntu,0 AS refcntd,1 as host
,CONVERT(varchar(10),GETDATE(),120) as date,getdate() as wdte1,getdate() as wdte2,2 as ISEX,case when isnull(ASN,'')='' then ' ' else ASN end as bpdn FROM 
( SELECT a.BillNo,a.Storage_SN,b.RelatedBill,b.PartNumber,b.RealQty AS Qty,b.RowNo,b.OrderNo,a.BillType,b.Unit,b.ASN,a.Ext3  FROM dbo.wms_T_InStockRM a
inner JOIN dbo.wms_T_InStockRMRows b ON a.BillNo=b.BillNo
WHERE a.Status='1' and BillType<>'退料入库' and BillType<>'非拉动退料入库' and BillType<>'分包入库' and b.RealQty>0) temp";

            var dt = SqlDBHelper.GetDataSet(sql).Tables[0];
            var result = MakeTablePackage(dt);
            return result;
        }

        public List<tddev102> GetSyncOutStockData()
        {
            var sql = @"SELECT BillNo as WMSPO, NEWID() AS guid,RelatedBill as orno,RowNo as pono,OrderNo as sqnb,'发料' as ittp  ,
PartNumber as item,temp.Unit cuqp, -temp.Qty reci,'2'as fire,ISNULL(Storage_SN,' ') as cwar, ' ' as rclo ,getdate() as edte1,' ' AS mess,0 AS refcntu,0 AS refcntd,1 as host
,CONVERT(varchar(10),GETDATE(),120) as date,getdate() as wdte1,getdate() as wdte2,2 as ISEX,' ' as bpdn FROM ( SELECT a.BillNo,a.Storage_SN,b.RelatedBill,b.PartNumber,b.RealQty AS Qty,b.RowNo,b.OrderNo,a.BillType,b.Unit  FROM dbo.wms_T_OutStockRM a
inner JOIN dbo.wms_T_OutStockRMRows b ON a.BillNo=b.BillNo
WHERE b.Status='1' and BillType='采购退货' ) temp";

            var dt = SqlDBHelper.GetDataSet(sql).Tables[0];
            var result = MakeTablePackage(dt);
            return result;
        }

        public bool UpdateUploadPOMOStatus()
        {
            var result = false;

            var sql = "UPDATE  wms_T_OutStockRMRowDetails SET Status='8' where Status='0' and billno in (select billno from wms_T_OutStockRM where billtype='采购退货') ";
            var updateRes = SqlDBHelper.ExcuteSQL(sql);
            if (updateRes > 0)
            {
                result = true;
            }
            return result;

        }
        public bool UpdateUploadStatus(List<tddev102> list)
        {
            var result = false;
            var billNoList = list.Select(x => x.WMSPO).Distinct();
            if (billNoList.Count() > 0)
            {
                var sqlWhere = "where BillNo in (";
                foreach (var item in billNoList)
                {
                    sqlWhere = sqlWhere + "'" + item + "',";
                }
                sqlWhere = sqlWhere.TrimEnd(',');
                sqlWhere = sqlWhere + ")";
                var sql = "UPDATE  wms_T_InStockRM SET Status='2'" + sqlWhere + ";";
                var updateSql = @"UPDATE  wms_T_InStockRM SET Status='4' " + sqlWhere + " and Storage_SN in (select Storage_SN from T_Bllb_Storage_tbs where Storage_Type = '201');";
                var strSql = sql + updateSql;
                var updateRes = SqlDBHelper.ExcuteSQL(strSql);
                if (updateRes > 0)
                {
                    result = true;
                }
            }
            return result;
        }

        public bool UpdateUploadStatus(tddev102 tddev)
        {
            var result = false;

            var sql = "UPDATE  wms_T_InStockRM SET Status='2' where BillNo = '" + tddev.WMSPO + "';";
            var updateSql = @"UPDATE  wms_T_InStockRM SET Status='4' where BillNo = '" + tddev.WMSPO + 
                "' and Storage_SN in (select Storage_SN from T_Bllb_Storage_tbs where Storage_Type = '201');";
            var strSql = sql + updateSql;
            var updateRes = SqlDBHelper.ExcuteSQL(strSql);
            if (updateRes > 0)
            {
                result = true;
            }
            return result;
        }

        //END
    }
}
